from sqlalchemy import Column, Integer, String, Float, DateTime, Text, ForeignKey, Boolean, JSON, func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
import datetime

Base = declarative_base()

class DianpingShop(Base):
    """大众点评店铺基础信息"""
    __tablename__ = 'dianping_shops'

    id = Column(Integer, primary_key=True, autoincrement=True)
    shop_id = Column(String(50), unique=True, index=True, comment='店铺ID')
    name = Column(String(100), nullable=False, comment='店铺名称')
    address = Column(String(255), comment='店铺地址')
    latitude = Column(Float, comment='纬度')
    longitude = Column(Float, comment='经度')
    avg_price = Column(Float, comment='人均消费金额')
    rating = Column(Float, comment='店铺评分')
    review_count = Column(Integer, comment='评论数量')
    category = Column(String(50), comment='店铺分类')
    phone = Column(String(50), comment='店铺电话')
    city = Column(String(50), comment='城市')
    district = Column(String(50), comment='行政区')
    business_area = Column(String(50), comment='商圈')
    
    created_at = Column(DateTime, default=datetime.datetime.now)
    updated_at = Column(DateTime, default=datetime.datetime.now, onupdate=datetime.datetime.now)
    
    # 关联店铺详情
    details = relationship("DianpingShopDetail", back_populates="shop", uselist=False)

class DianpingShopDetail(Base):
    """大众点评店铺详情信息"""
    __tablename__ = 'dianping_shop_details'
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    shop_id = Column(String(50), ForeignKey('dianping_shops.shop_id'), unique=True, index=True)
    open_hours = Column(String(255), comment='营业时间')
    facilities = Column(Text, comment='设施与服务')
    introduction = Column(Text, comment='店铺简介')
    features = Column(Text, comment='特色服务')
    payment_methods = Column(String(255), comment='支付方式')
    tags = Column(String(255), comment='店铺标签')
    photos_count = Column(Integer, comment='照片数量')
    raw_data = Column(JSON, comment='原始数据JSON')
    
    created_at = Column(DateTime, default=datetime.datetime.now)
    updated_at = Column(DateTime, default=datetime.datetime.now, onupdate=datetime.datetime.now)
    
    # 关联店铺
    shop = relationship("DianpingShop", back_populates="details")

class FoodDeliveryShop(Base):
    """外卖平台店铺信息"""
    __tablename__ = 'food_delivery_shops'
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    platform = Column(String(50), comment='平台名称，如美团、饿了么等')
    shop_id = Column(String(50), index=True, comment='店铺ID')
    name = Column(String(100), nullable=False, comment='店铺名称')
    address = Column(String(255), comment='店铺地址')
    category = Column(String(50), comment='店铺分类')
    month = Column(String(7), comment='数据月份，格式YYYY-MM')
    
    # 月度指标
    monthly_orders = Column(Integer, comment='月度订单量')
    monthly_sales = Column(Float, comment='月度销售额')
    avg_price = Column(Float, comment='人均价格')
    rating = Column(Float, comment='店铺评分')
    
    created_at = Column(DateTime, default=datetime.datetime.now)
    updated_at = Column(DateTime, default=datetime.datetime.now, onupdate=datetime.datetime.now)
    
    # 唯一约束：平台+店铺ID+月份
    __table_args__ = (
        {'mysql_charset': 'utf8mb4', 'mysql_collate': 'utf8mb4_unicode_ci'},
    )

class FoodDeliverySKU(Base):
    """外卖平台SKU销售数据"""
    __tablename__ = 'food_delivery_skus'
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    platform = Column(String(50), comment='平台名称，如美团、饿了么等')
    shop_id = Column(String(50), index=True, comment='店铺ID')
    sku_id = Column(String(50), index=True, comment='SKU ID')
    name = Column(String(100), nullable=False, comment='商品名称')
    category = Column(String(50), comment='商品分类')
    month = Column(String(7), comment='数据月份，格式YYYY-MM')
    
    # 月度指标
    monthly_sales_count = Column(Integer, comment='月度销量')
    monthly_sales_amount = Column(Float, comment='月度销售额')
    price = Column(Float, comment='单价')
    discount_price = Column(Float, comment='折扣价')
    
    created_at = Column(DateTime, default=datetime.datetime.now)
    updated_at = Column(DateTime, default=datetime.datetime.now, onupdate=datetime.datetime.now)
    
    # 唯一约束：平台+店铺ID+SKUID+月份
    __table_args__ = (
        {'mysql_charset': 'utf8mb4', 'mysql_collate': 'utf8mb4_unicode_ci'},
    )

class CrawlTask(Base):
    """爬虫任务记录"""
    __tablename__ = 'crawl_tasks'
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    task_name = Column(String(100), comment='任务名称')
    spider_name = Column(String(50), comment='爬虫名称')
    task_type = Column(String(50), comment='任务类型')
    status = Column(String(20), comment='状态：pending, running, completed, failed')
    start_time = Column(DateTime, comment='开始时间')
    end_time = Column(DateTime, comment='结束时间')
    params = Column(JSON, comment='任务参数')
    result = Column(JSON, comment='任务结果统计')
    error_message = Column(Text, comment='错误信息')
    
    created_at = Column(DateTime, default=datetime.datetime.now)
    updated_at = Column(DateTime, default=datetime.datetime.now, onupdate=datetime.datetime.now)


class DataSourceConfig(Base):
    """数据源配置"""
    __tablename__ = 'data_source_configs'
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(100), comment='配置名称')
    source_type = Column(String(50), comment='数据源类型: dianping, meituan, eleme等')
    config = Column(JSON, comment='配置参数JSON')
    is_active = Column(Boolean, default=True, comment='是否启用')
    description = Column(Text, comment='配置描述')
    
    created_at = Column(DateTime, default=datetime.datetime.now)
    updated_at = Column(DateTime, default=datetime.datetime.now, onupdate=datetime.datetime.now) 